# 下载http://www.sse.com.cn/搜索内容的pdf,并信息存pg库
import requests
import json
import os
import re
from datetime import datetime, timedelta
import time

# 调试路径
import sys
project_root = os.path.abspath(os.path.join(os.path.dirname(__file__), '../../'))
sys.path.append(project_root)
from postgresql.postgresql import Postgresql

Postgresqlhost = os.environ.get('Postgresqlhost')
Postgresqldatabase = os.environ.get('Postgresqldatabase')
Postgresqluser = os.environ.get('Postgresqluser')
Postgresqlpassword = os.environ.get('Postgresqlpassword')
# StartTime = os.environ.get('StartTime')
Postgresqlhost=""
Postgresqldatabase="esg"
Postgresqluser="sumz"
Postgresqlpassword="Sumz"

StartTime = "2024-01-01"

# 获取当前日期
today = datetime.now()
# 计算昨天日期
yesterday = today - timedelta(days=1)
# 将昨天日期格式化为字符串
yesterday_str = yesterday.strftime("%Y-%m-%d")

if StartTime:
    startday_str = StartTime
else:
    startday_str = yesterday_str

# keywords_list = ["ESG", "社会责任", "可持续发展", "环境、社会及治理", "环境、社会及管治", "年报", "年度报告"]
keywords_list = ["年度报告"]

URL = 'http://www.sse.com.cn'

headers = {
    'Referer': URL,
    'User-Agent':'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/121.0.0.0'
}
#00,0101,0102,0104,0103
def downlourl(title, page='1', pageSize=100):
    url = f"https://query.sse.com.cn/security/stock/queryCompanyBulletinNew.do?jsonCallBack=jsonpCallback63978175&isPagination=true&pageHelp.pageSize={pageSize}&pageHelp.cacheSize=1&START_DATE={startday_str}&END_DATE={yesterday_str}&SECURITY_CODE=&TITLE=&BULLETIN_TYPE=&stockType=&pageHelp.pageNo={page}&pageHelp.beginPage={page}&pageHelp.endPage={page}&_=1716436548129"
    return(url)

def get_data_json(url):
  response = requests.get(url,headers=headers)
  response.encoding = 'utf-8'
  text=response.text
  json_text = re.sub(r'^jsonpCallback\d+\((.*)\)$', r'\1', text)
  json_data = json.loads(json_text)
  return json_data

def generate_time_strings(start_date_str):
    time_strings = []
    current_date = datetime.strptime(start_date_str, '%Y-%m-%d')
    end_date = datetime.now()
    while True:
        current_date_swap = current_date
        current_date += timedelta(days=90)  # 每三个月为90天
        if current_date >= end_date:
            break
        time_strings.append([current_date_swap.strftime('%Y-%m-%d'), current_date.strftime('%Y-%m-%d')])
            
    return time_strings

time_strings_list = generate_time_strings(startday_str)

for keywords_list_one in keywords_list:
    print(f"【下载开始】【关键字】【{keywords_list_one}】")
    # 第一次爬取，获取数量
    init_data_info_json = get_data_json(downlourl(keywords_list_one))

    # 第二次爬取，获取所有数量的列表
    total_page = init_data_info_json['pageHelp']['pageCount']
    json_data_list = []
    for total_page_one in range(1, int(total_page)):
        json_data = get_data_json(downlourl(keywords_list_one, page=total_page_one))['pageHelp']['data']
        for json_data_one in json_data:
            json_data_list += json_data_one
        time.sleep(5)
        # break

    all_down_url = []
    all_down_pdf_title = []
    all_down_pdf_updatetime = []
    company_name_list = []
    all_label = []
    type_pdf_list = []
    lines_list = []

    for knowledgeList_one in json_data_list:
        down_pdf_url = URL + knowledgeList_one['URL']
        down_pdf_title = knowledgeList_one['TITLE'].replace("_", "").replace(" ", "")
        down_pdf_updatetime = knowledgeList_one['SSEDATE']
        company_name = knowledgeList_one['SECURITY_NAME']
        type_pdf = knowledgeList_one['BULLETIN_TYPE_DESC']
        

        if down_pdf_url in all_down_url:
            continue
    
        # 排除非文件的信息
        if not knowledgeList_one['SECURITY_CODE']:
            continue

        if down_pdf_url.endswith(".pdf"):
            lines_list.append([knowledgeList_one['SECURITY_CODE'] + '.SH', 'sse', down_pdf_url, down_pdf_updatetime, down_pdf_title, company_name, type_pdf])

    # 创建Postgresql实例并传入连接参数
    postgresql = Postgresql(host=Postgresqlhost, database=Postgresqldatabase, user=Postgresqluser, password=Postgresqlpassword)
    # 创建连接和游标对象
    postgresql.create_connection()

    print(f"【下载完成】【关键字】【{keywords_list_one}】")
    
    # 示例插入数据
    for lines_list_one in lines_list:
        data = {
            "stock_code": lines_list_one[0], 
            "company_report_type": lines_list_one[1], 
            "company_report_path": lines_list_one[2],
            "file_time": lines_list_one[3],
            "file_name": lines_list_one[4],
            "company_name": lines_list_one[5],
            "keyword": lines_list_one[6]
                }
        result = postgresql.execute_insert("company_report_swap2", data)
        postgresql.execute_commit()

    # 关闭连接
    postgresql.quit_connection()
    print(f"【数据入库完成】【关键字】【{keywords_list_one}】")
